SQLite使用(二)&&数据类型

1.概述

      我们熟知的数据库引擎大部分采用静态数据类型，即列定义的类型定义了值的存储，并且值要严格满足列的定义，同一列所有值的存储方式都相同，比如定义了一个列类型为整型 int，不能在该列上输入'abc'。SQLite的数据类型则采用了动态类型，列定义不能决定值的存储，值的存储由值本身决定，因此在SQLite中，同一列会有多种存储方式。 

2.数据类型与存储类

SQLite根据值的存储划分为以下几种存储类型：

存储类别	含义
NULL	表示该值为NULL
INTEGER	有符号整型值，根据值大小控制存储占用的字节数，可能为1，2，3，4，6 or 8个字节
REAL	浮点值，采用8个字节存储，实质是采用double存储，有效数字为15位
TEXT	文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE，默认最大可以存储1G个字节
BLOB	存储二进制数据，默认最大可以存储1G个字节

     在SQLite中，存储分类和数据类型不是完全等价的，如INTEGER存储类别可以包含6种不同长度的Integer数据类型，然而这些INTEGER数据一旦被读入到内存后，SQLite会将其全部视为占用8个字节有符号整型。因此对于SQLite而言，同一个字段类型，可以在该字段中存储不停类型的数据，而且即便值的存储类型相同，底层存储占用的空间也与值相关，比如有的INTEGER占用1个字节，有的INTEGER可能占用8个字节。

(1).布尔数据类型：

SQLite并没有提供专门的布尔存储类型，取而代之的是存储整型1表示true，0表示false。

(2).日期和时间数据类型：

和布尔类型一样，SQLite也同样没有提供专门的日期时间存储类型，而是以TEXT、REAL和INTEGER类型分别不同的格式表示该类型，如：

TEXT: "YYYY-MM-DD HH:MM:SS.SSS"

REAL: 以Julian日期格式存储

INTEGER: 以Unix时间形式保存数据值，即从1970-01-01 00:00:00到当前时间所流经的秒数。

SQLite提供typeof函数，用户可以根据这个函数来确定给定值的存储类型。    

3.类型亲缘性

      为了最大化SQLite和其它数据库引擎之间的数据类型兼容性，SQLite提出了"类型亲缘性(Type Affinity)"的概念。我们可以这样理解"类型亲缘性 "，在表字段被声明之后，SQLite都会根据该字段声明时的类型为其选择一种亲缘类型，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式，除非亲缘类型不匹配或无法转换当前数据到该亲缘类型，这样SQLite才会考虑其它更适合该值的类型存储该值。SQLite目前的版本支持以下五种亲缘类型：

亲缘类型	描述
TEXT	数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER	对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE	不做任何的转换，直接以该数据所属的数据类型进行存储。

3.1.字段亲缘性的规则

      字段的亲缘性是根据该字段在声明时被定义的类型来决定的，具体的规则可以参照以下列表。需要注意的是以下列表的顺序，即如果某一字段类型同时符合两种亲缘性，那么排在前面的规则将先产生作用。
    1). 如果类型字符串中包含"INT"，那么该字段的亲缘类型是INTEGER。
    2). 如果类型字符串中包含"CHAR"、"CLOB"或"TEXT"，那么该字段的亲缘类型是TEXT，如VARCHAR。
    3). 如果类型字符串中包含"BLOB"，那么该字段的亲缘类型是NONE。
    4). 如果类型字符串中包含"REAL"、"FLOA"或"DOUB"，那么该字段的亲缘类型是REAL。
    5). 其余情况下，字段的亲缘类型为NUMERIC。
3.2.具体示例

​声明类型	亲缘类型	应用规则
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

4.比较与排序

     在SQLite3中支持的比较表达式有："=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS" and "IS NOT"。数据的比较结果主要依赖于操作数的存储方式，其规则为：

1). 存储方式为NULL的数值小于其它存储类型的值。

2). 存储方式为INTEGER和REAL的数值小于TEXT或BLOB类型的值，如果同为INTEGER或REAL，则基于数值规则进行比较。

3). 存储方式为TEXT的数值小于BLOB类型的值。

4). 如果是两个BLOB类型的数值进行比较，其结果为C运行时函数memcmp()的结果。

5). 如果同为TEXT，SQLite利用特定的比较规则来判断，支持3种比较规则：

校对规则	含义
binary	比较字符串采用memcmp()，无论字符串是哪种编码
nocase	与binary类似，但忽略大小写
rtrim	与binary类似，但忽略字符串末尾的空格

通过建表语句在可以在指定列上指定校对规则，比如：

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);

5.参考文档

https://www.sqlite.org/datatype3.html

http://www.cnblogs.com/stephen-liu74/archive/2012/01/18/2325258.html